package com.popcorn.rmsystem.dao;
// DB 연결
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.popcorn.rmsystem.model.RMSystemModel;

public class RMSystemDAO {
		Connection conn = null;
		PreparedStatement pstmt = null;
		DataSource ds = null;
		ResultSet rs = null;
		
		public RMSystemDAO(){
		try{
			Context init = new InitialContext();
			ds = (DataSource)init.lookup("java:comp/env/jdbc/Mysql");
			         
		}catch(Exception e){
			e.printStackTrace();
			}	  
		}

		//글의 개수 구하기
		public int getListCount() {
			int x= 0;
			
			try{
				
				conn=ds.getConnection();
				System.out.println("getConnection");
				pstmt=conn.prepareStatement("select count(*) from reservation");
				rs = pstmt.executeQuery();
				
				if(rs.next()){
					x=rs.getInt(1);
				}
			}catch(Exception ex){
				System.out.println("getListCount 에러: " + ex);			
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return x;
		}
		
		//예약 리스트 보기
		public List modelList(int page,int limit){	
			String sql="";			
			List list = new ArrayList();			
//			int startrow=(page-1)*10+1; //읽기 시작할 row번호.
//			int endrow=startrow+limit-1; //읽을 마지막 row 번호.	
			try{
				try {
					conn = ds.getConnection();				
				} catch (Exception e) {
					System.out.println("connect Exception : "+e.toString());
				}
				 
				sql="select * from reservation order by RESERVATION_SEQ desc";				
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				
				while(rs.next()){
					RMSystemModel model = new RMSystemModel();
					model.setRMS_IDX(rs.getInt("RESERVATION_SEQ"));
					model.setTEAM_NAME(rs.getString("TEAM_NAME"));
					model.setNAME(rs.getString("NAME"));
					model.setHP(rs.getString("HP"));
					model.setTRANDATE(rs.getString("TRANDATE"));
					model.setSTARTTIME(rs.getInt("STARTTIME"));
					model.setENDTIME(rs.getInt("ENDTIME"));
					model.setROOMSIZE(rs.getString("ROOMSIZE"));
					list.add(model);
				}				
				return list;
				
			}catch(Exception ex){
				System.out.println("보기 실패");
				ex.printStackTrace();
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return null;
		}
		
		//팀명 검색
		public List modelSelect_TeamName(String TEAM_NAME) {
			String sql="";			
			List list = new ArrayList();
			
			try{
				try {
					conn = ds.getConnection();				
				} catch (Exception e) {
					System.out.println("connect Exception : "+e.toString());
				}
				 
				sql="select * from reservation where TEAM_NAME ='"
						+TEAM_NAME
						+"' order by RESERVATION_SEQ";				
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				
				while(rs.next()){
					RMSystemModel model = new RMSystemModel();
					model.setRMS_IDX(rs.getInt("RESERVATION_SEQ"));
					model.setTEAM_NAME(rs.getString("TEAM_NAME"));
					model.setNAME(rs.getString("NAME"));
					model.setHP(rs.getString("HP"));
					model.setTRANDATE(rs.getString("TRANDATE"));
					model.setSTARTTIME(rs.getInt("STARTTIME"));
					model.setENDTIME(rs.getInt("ENDTIME"));
					model.setROOMSIZE(rs.getString("ROOMSIZE"));
					list.add(model);
				}				
				return list;
				
			}catch(Exception ex){
				System.out.println("보기 실패");
				ex.printStackTrace();
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return null;
		}
		
		//날짜만 검색
		public List modelSelect_Date(String TRANDATE) {
			String sql="";			
			List list = new ArrayList();
			
			try{
				try {
					conn = ds.getConnection();				
				} catch (Exception e) {
					System.out.println("connect Exception : "+e.toString());
				}
				 
				sql="select * from reservation where TRANDATE ='"
						+TRANDATE
						+"' order by RESERVATION_SEQ";				
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				
				while(rs.next()){
					RMSystemModel model = new RMSystemModel();
					model.setRMS_IDX(rs.getInt("RESERVATION_SEQ"));
					model.setTEAM_NAME(rs.getString("TEAM_NAME"));
					model.setNAME(rs.getString("NAME"));
					model.setHP(rs.getString("HP"));
					model.setTRANDATE(rs.getString("TRANDATE"));
					model.setSTARTTIME(rs.getInt("STARTTIME"));
					model.setENDTIME(rs.getInt("ENDTIME"));
					model.setROOMSIZE(rs.getString("ROOMSIZE"));
					list.add(model);
				}				
				return list;
				
			}catch(Exception ex){
				System.out.println("보기 실패");
				ex.printStackTrace();
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return null;
		}
		
		//날짜,시간 검색
		public List modelSelect_Date_Time(String TRANDATE,
									int STARTTIME, int ENDTIME) {
			String sql="";			
			List list = new ArrayList();
			
			try{
				try {
					conn = ds.getConnection();				
				} catch (Exception e) {
					System.out.println("connect Exception : "+e.toString());
				}
				 
				sql="select * from reservation where TRANDATE ='"
						+TRANDATE+"' and "
						+"STARTTIME >="+STARTTIME+" or ENDTIME <="+ENDTIME
						+" order by RESERVATION_SEQ";				
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				
				while(rs.next()){
					RMSystemModel model = new RMSystemModel();
					model.setRMS_IDX(rs.getInt("RESERVATION_SEQ"));
					model.setTEAM_NAME(rs.getString("TEAM_NAME"));
					model.setNAME(rs.getString("NAME"));
					model.setHP(rs.getString("HP"));
					model.setTRANDATE(rs.getString("TRANDATE"));
					model.setSTARTTIME(rs.getInt("STARTTIME"));
					model.setENDTIME(rs.getInt("ENDTIME"));
					model.setROOMSIZE(rs.getString("ROOMSIZE"));
					list.add(model);
				}				
				return list;
				
			}catch(Exception ex){
				System.out.println("보기 실패");
				ex.printStackTrace();
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return null;
		}
		
		
		//스터디 idx 상세내용 보기
		public RMSystemModel modelDetail(int idx){
			String sql="";
			RMSystemModel model = new RMSystemModel();
			try{
				try {
					conn = ds.getConnection();				
				} catch (Exception e) {
					System.out.println("connect Exception : "+e.toString());
				}
				 
				sql="select * from reservation where RESERVATION_SEQ="+idx;				
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
			
				if(rs.next()){
					model.setRMS_IDX(rs.getInt("RESERVATION_SEQ"));
					model.setTEAM_NAME(rs.getString("TEAM_NAME"));
					model.setNAME(rs.getString("NAME"));
					model.setHP(rs.getString("HP"));
					model.setTRANDATE(rs.getString("TRANDATE"));
					model.setSTARTTIME(rs.getInt("STARTTIME"));
					model.setENDTIME(rs.getInt("ENDTIME"));
					model.setROOMSIZE(rs.getString("ROOMSIZE"));
					return model;
				}
				
					return null;
					
			}catch(Exception ex){
				System.out.println("상세보기 실패");
				ex.printStackTrace();
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return model;
		}
		
		//스터디 예약 등록
		public boolean modelInsert(RMSystemModel model){
			int result = 0;
			int num =0;
			String sql="";			
			
			try{
				try {
					conn = ds.getConnection();				
				} catch (Exception e) {
					System.out.println("connect Exception : "+e.toString());
				}

				pstmt=conn.prepareStatement("select * from reservation where TRANDATE= '"+ model.getTRANDATE()
														+ "' and STARTTIME>="+ model.getSTARTTIME()
														+ " or ENDTIME<="+ model.getENDTIME()
														+ " and ROOMSIZE ='"+ model.getROOMSIZE() +"'");
				rs = pstmt.executeQuery();
				if(rs.next()) return false;
				
				pstmt=conn.prepareStatement("select max(RESERVATION_SEQ) from reservation");
				rs = pstmt.executeQuery();				
				
				if(rs.next()) num =rs.getInt(1)+1;
				else num=1;
				
				sql="insert into reservation values(?,?,?,?,?,?,?,?)";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, num);
				pstmt.setString(2, model.getTEAM_NAME());
				pstmt.setString(3, model.getNAME());
				pstmt.setString(4, model.getHP());
				pstmt.setString(5, model.getTRANDATE());
				pstmt.setInt(6, model.getSTARTTIME());
				pstmt.setInt(7, model.getENDTIME());
				pstmt.setString(8, model.getROOMSIZE());
				
				System.out.println("====================InsertDB====================");
				System.out.println("1 : "+num);
				System.out.println("2 : "+model.getTEAM_NAME());
				System.out.println("3 : "+model.getNAME());
				System.out.println("4 : "+model.getHP());
				System.out.println("5 : "+model.getTRANDATE());
				System.out.println("6 : "+model.getSTARTTIME());
				System.out.println("7 : "+model.getENDTIME());
				System.out.println("8 : "+model.getROOMSIZE());
				result=pstmt.executeUpdate();
				
				if(result==0)return false;
				
				return true;
				
			}catch(Exception ex){
				System.out.println("등록 실패");
				ex.printStackTrace();
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return false;
		}
		// 스터디 예약 수정
		public boolean modelUpdate(RMSystemModel model){		
			int result = 0;		
			String sql="";			
			
			try{
				try {
					conn = ds.getConnection();				
				} catch (Exception e) {
					System.out.println("connect Exception : "+e.toString());
				}
				pstmt=conn.prepareStatement("select * from reservation where RESERVATION_SEQ =" + model.getRMS_IDX());
				rs = pstmt.executeQuery();
				if(rs.next()){
					System.out.println("1 SQL");
					if(!rs.getString("TRANDATE").equals(model.getTRANDATE())){
						System.out.println("1-1 SQL");
						pstmt=conn.prepareStatement("select * from reservation where TRANDATE= '"+ model.getTRANDATE()
								+ "' and STARTTIME>="+ model.getSTARTTIME()
								+ " or ENDTIME<="+ model.getENDTIME()
								+ " and ROOMSIZE = '"+model.getROOMSIZE()+"'"
								+ " and RESERVATION_SEQ !="+ model.getRMS_IDX());
						rs = pstmt.executeQuery();
						if(rs.next()) return false;
						
						sql="update reservation set TRANDATE= ?, STARTTIME= ?, ENDTIME= ? where RESERVATION_SEQ = ?";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, model.getTRANDATE());
						pstmt.setInt(2, model.getSTARTTIME());
						pstmt.setInt(3, model.getENDTIME());
						pstmt.setInt(4, model.getRMS_IDX());
						System.out.println("====================UpdateDB====================");
						System.out.println("1 : "+ model.getTRANDATE());
						System.out.println("2 : "+model.getSTARTTIME());
						System.out.println("3 : "+model.getENDTIME());
						System.out.println("4 : "+ model.getRMS_IDX());
						result=pstmt.executeUpdate();
					}
					if(!rs.getString("ROOMSIZE").equals(model.getROOMSIZE())){
						System.out.println("1-2 SQL");
						pstmt=conn.prepareStatement("select * from reservation where TRANDATE= '"+ model.getTRANDATE()
								+ "' and ( STARTTIME>="+ model.getSTARTTIME()
								+ " or ENDTIME<="+ model.getENDTIME()
								+ " ) and ROOMSIZE = '"+model.getROOMSIZE()+"'"
								+ " and RESERVATION_SEQ !="+ model.getRMS_IDX());
						rs = pstmt.executeQuery();
						if(rs.next()) return false;
						
						sql="update reservation set ROOMSIZE= ? where RESERVATION_SEQ = ?";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, model.getROOMSIZE());
						pstmt.setInt(2, model.getRMS_IDX());
						System.out.println("====================UpdateDB====================");
						System.out.println("1 : "+ model.getTRANDATE());
						System.out.println("2 : "+ model.getROOMSIZE());
						result=pstmt.executeUpdate();
					}
				}	
				if(result==0)return false;
				
				return true;
				
			}catch(Exception ex){
				System.out.println("수정 실패");
				ex.printStackTrace();
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return false;
		}		
			
		public boolean modelDelete(int idx){			
			int result = 0;		
			String sql="";			
			
			try{
				try {
					conn = ds.getConnection();				
				} catch (Exception e) {
					System.out.println("connect Exception : "+e.toString());
				}
				
				sql="delete from reservation where RESERVATION_SEQ = ?";
				
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, idx);
				System.out.println("====================deleteDB====================");
				System.out.println("1 : "+ idx);
				result=pstmt.executeUpdate();
				
				if(result==0)return false;
				
				return true;
				
			}catch(Exception ex){
				System.out.println("삭제 실패");
				ex.printStackTrace();
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return false;
		}

		public boolean sessionCheck(String nickname, String pwd, String idx) {
			String sql="";			
			
			try{
				try {
					conn = ds.getConnection();				
				} catch (Exception e) {
					System.out.println("connect Exception : "+e.toString());
				}
				
				sql="select * from reservation where RESERVATION_SEQ="+idx;
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next())
					if(!rs.getString("NAME").equals(nickname)){
						System.out.println("접근 권한 없음");
						return false;
					}
				
				sql="select * from member where NICKNAME ='"
						+nickname+"' and PASSWORD =MD5('"
						+pwd+"')";				
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				
				while(rs.next()){	
				return true;
				}
				
			}catch(Exception ex){
				System.out.println("세션 확인 실패");
				ex.printStackTrace();
			}finally{
				if(rs!=null) try{rs.close();}catch(SQLException ex){}
				if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}
				if(conn!=null) try{conn.close();}catch(SQLException ex){}
			}
			return false;
		}		
}